/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Objet;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Iterator;
import java.io.*; 

import java.util.StringTokenizer;

//import org.jexcelapi.*; 
//import jxl.*; 
//import jxl.write.*; 

/**
 *
 * @author zouafr
 */
public class QueryBD {
    

    /** Paramètre de connection à la base */
    
    String nomDriver = "oracle.jdbc.driver.OracleDriver";
    
    //String url="jdbc:oracle:thin:@miage03.dmiage.u-paris10.fr:1521:miage";
    //String user="zouafr"; 
    //String mdp="apprentis2012pw";
    String url="jdbc:oracle:thin:@localhost:1521/xe";
    String user="ma_base"; 
    String mdp="violet";

    Connection cx; 
    
    
    
    public QueryBD() 
    {
        try
        {
            Class.forName(nomDriver);
        }
        catch(ClassNotFoundException ex)
        {
            System.out.println("La classe "+nomDriver+" n'a pu etre trouvée");
            ex.printStackTrace();
        }
        
        try
        {    
            cx = DriverManager.getConnection(url,user,mdp);
        }
        catch(SQLException ex)
        {
            System.err.println("Echec de connexion a la base");
            System.exit(1);
            
        }
    }
    
    
    public Connection connect() {
        try
        {
            Class.forName(nomDriver);
        }
        catch(ClassNotFoundException ex)
        {
            System.out.println("La classe "+nomDriver+" n'a pu etre trouvée");
            ex.printStackTrace();
        }
        
        try
        {    
            cx = DriverManager.getConnection(url,user,mdp);
            return cx;
        }
        catch(SQLException ex)
        {
            System.err.println("Echec de connexion a la base");
            System.exit(1);
            
        }
        return cx;
    }
    
    public Hashtable listeEtudiants()
    {
        Hashtable<String,String> listEtudiants= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM Etudiant");

            while(rs.next())
            {
                listEtudiants.put(rs.getString("login"),rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des etudiants");
            System.exit(1);
        }
        return listEtudiants;
    }
    
    
    public Hashtable listeEnseignant()
    {
        Hashtable<String,String> listEnseignant= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM Enseignant");
            
            while(rs.next())
            {
                listEnseignant.put(rs.getString("login"),rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des enseignants.");
            System.exit(1);
        }
        return listEnseignant;
    }
    
    public Hashtable listeSecretaire()
    {
        Hashtable<String,String> listSecretaire= new Hashtable();
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT login, password FROM secretaire");
            
            
            while(rs.next())
            {
                System.out.println("OKOOOOOOOOOOOOOOOOOK");
                
                listSecretaire.put(rs.getString("login"),rs.getString("password"));
                
                System.out.println(rs.getString("login")+""+rs.getString("password"));
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL du secretaire.");
            System.exit(1);
        }
        return listSecretaire;
        
    }
    
    public Etudiant creerEtudiant(String login, String mdp){
        
        Etudiant NewEtu = new Etudiant();
        
        try{

            Statement st = cx.createStatement();
            ResultSet rs =  st.executeQuery
                    ("Select * FROM ETUDIANT "
                      + "WHERE login='"+login+"' AND password='"+mdp+"'");
            rs.next();

            NewEtu.setNom(rs.getString("nom"));
            NewEtu.setPrenom(rs.getString("prenom"));
            NewEtu.setNetudiant(rs.getInt("netudiant"));
            NewEtu.setLogin(rs.getString("login"));
            NewEtu.setMdp(rs.getString("password"));

            st.close();
            rs.close();
            
                 
        }
        catch (SQLException ex) {
            System.err.println(":/ Probleme requete SQL recupération des notes."+ex);
            System.exit(1);
        } 
        return NewEtu;
    }
    
    public Enseignant creerEnseignant(String login, String password)
    {
         Enseignant NewEnseignant= new Enseignant();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM ENSEIGNANT "
                       + "WHERE login='"+login+"'AND password='"+password+"'");
                rs.next();

                NewEnseignant.setNom(rs.getString("nom"));
                NewEnseignant.setPrenom(rs.getString("prenom"));
                NewEnseignant.setNumen(rs.getString("numen"));
                NewEnseignant.setLogin(rs.getString("login"));
                NewEnseignant.setMdp(rs.getString("password"));

                st.close();
                rs.close();

                
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL création de l'enseignant."+ex);
                System.exit(1);
        }
        return NewEnseignant;
    }
    
    public Secretaire creerSecretaire(String login, String password)
    {
         Secretaire NewSecretaire= new Secretaire();
         try{

                Statement st = cx.createStatement();
                ResultSet rs =  st.executeQuery
                        ("Select * FROM Secretaire"
                       + "WHERE login='"+login+"'AND password='"+password+"'");
                rs.next();

                NewSecretaire.setNom(rs.getString("nom"));
                NewSecretaire.setPrenom(rs.getString("prenom"));
                NewSecretaire.setNumAD(rs.getInt("numAD"));
                NewSecretaire.setLogin(rs.getString("login"));
                NewSecretaire.setMdp(rs.getString("password"));

                st.close();
                rs.close();
                System.out.println("ok");
                System.out.println("_");

                /*Statement st2 = cx.createStatement();
                int numAD = NewSecretaire.getNumAD();
                
                ResultSet rs2 = st2.executeQuery
                    ("Select * FROM Absence ;");

                ArrayList<Absence> lesAbsences = new ArrayList<Absence>();

                while(rs2.next()){
                
                    lesAbsences.add(new Absence(rs2.getInt("id_absence"),
                                rs2.getString("statut"),
                                rs2.getString("creneau")));
                                        

                }
                System.out.println("aie 4");
                NewSecretaire.setListAbsence(lesAbsences);
                st2.close();
                rs2.close();*/
        }
        catch (SQLException ex) {
                System.err.println("Probleme requete SQL création du secretaire."+ex);
                System.exit(1);
        }
        return NewSecretaire;
    }
   
    
    public ArrayList<Absence> lesAbsences()
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * FROM Absence INNER JOIN Creneau ON Absence.id_creneau = creneau.id_creneau");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(rs1.getInt("nabsence"),
                                          rs1.getInt("netudiant"),
                                          rs1.getString("id_creneau"),
                                          rs1.getString("statut"),
                                          rs1.getInt("NENSEIGNANT"),
                                          
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations de toute les absences :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
    public ArrayList<Absence> lesAbsencesEns(String id_enseignant)
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * FROM Absence INNER JOIN Creneau ON Absence.id_creneau = creneau.id_creneau "
                             + "WHERE creneau.nenseignant="+id_enseignant+" ORDER BY Module");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(rs1.getInt("nabsence"),
                                          rs1.getInt("netudiant"),
                                          rs1.getString("id_creneau"),
                                          rs1.getString("statut"),
                                          rs1.getInt("NENSEIGNANT"),
                                          
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des absences enseignants :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
   public ArrayList<Absence> lesAbsencesEtu(int id_etudiant)
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select * FROM Absence INNER JOIN Creneau ON Absence.id_creneau = creneau.id_creneau "
                             + "WHERE absence.netudiant="+id_etudiant+" ORDER BY Module");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(rs1.getInt("nabsence"),
                                          rs1.getInt("netudiant"),
                                          rs1.getString("id_creneau"),
                                          rs1.getString("statut"),
                                          rs1.getInt("NENSEIGNANT"),
                                          
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des absences etudiant :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    } 
   
   public ArrayList<Absence> lesCreneaux(String id_enseignant)
    {
        ArrayList<Absence> mesAbsences = new ArrayList<Absence>();
        try{ 
                Statement st1 = cx.createStatement();
                ResultSet rs1 = st1.executeQuery
                     (" Select distinct  * FROM Creneau  "
                             + "WHERE creneau.nenseignant="+id_enseignant+" ORDER BY module");
                //System.out.println("can do requete");

                while(rs1.next())
                {
                                        Absence a = new Absence(

                                          rs1.getString("id_creneau"),
                                          rs1.getInt("NENSEIGNANT"),                                       
                                          rs1.getString("module"),                                    
                                          rs1.getString("heure_debut"),
                                          rs1.getString("heure_fin"),
                                          rs1.getDate("date_creneau")
                                          );                            
                                        mesAbsences.add(a);
                }
                //System.out.println("can do absence");
            }
            catch (SQLException ex) 
            {
                System.err.println
                       ("Erreur SQL recupérations des creneaux enseignants :"+ex);
                System.exit(1);
            }
            return mesAbsences;
    }
    
    
    
    public String nomEtudiant(int id) {
       Connection cx=connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select nom from etudiant "
                   + "where Netudiant="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("nom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération nom impossible, id étudiant non trouvé: "+ex);
       }
       return intitule;
    }
    
    public String prenomEtudiant(int id) {
       Connection cx= connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select prenom from etudiant "
                   + "where Netudiant="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("prenom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération prenom impossible, id étudiant non trouvé: "+ex);
       }
       return intitule;
    }
    
    public String nomEnseignant(int id) {
       Connection cx=connect();
       String intitule = null;
       try{
           java.sql.Statement st = cx.createStatement();
           ResultSet rs= st.executeQuery("Select nom from enseignant "
                   + "where Numen="+id+"");
           
           while(rs.next()){
               intitule=rs.getString("nom");
           }
           rs.close();
           st.close();
           cx.close();
       }catch(SQLException ex){
           System.out.println("Erreur récupération nom impossible, id enseignant non trouvé: "+ex);
       }
       return intitule;
    }
    
    public String maPromo(String id_creneau)
    {
        String Promotion = null;
        try{
            
            Statement st = cx.createStatement();
            ResultSet rs = st.executeQuery("SELECT npromotion FROM Creneau "+
                    "WHERE creneau.id_creneau="+id_creneau+"");

           while(rs.next()){
               Promotion=rs.getString("npromotion");
           }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL recupération id promotion:"+ex);
            System.exit(1);
        }
        return Promotion;
    }
        
    public ArrayList<Etudiant> listeEtudiantsPromo(String id_promotion)
    {
        ArrayList<Etudiant> listEtudiants= new ArrayList<Etudiant>();
        try {   
            Statement st = cx.createStatement();
            
            ResultSet rs = st.executeQuery("SELECT * FROM Etudiant "
                    + "INNER JOIN Appartient_Promo ON Etudiant.Netudiant = Appartient_Promo.Netudiant"
             + "WHERE Appartient_Promo.nompromo="+id_promotion+" ORDER BY nom, prenom"+"");
            System.out.println("ok ");
            while(rs.next())
            {
                System.out.println("okgg ");
                Etudiant a = new Etudiant(
                rs.getString("nom"),
                rs.getString("prenom"),      
                rs.getInt("Netudiant") ,
                rs.getString("login"),
                rs.getString("password")         
                );                            
                listEtudiants.add(a);
            }
            
            rs.close();
            st.close();
        }
        catch(SQLException ex){
            System.err.println(":/ Probleme requete SQL des etudiants de la promo:"+ex);
            System.exit(1);
        }
        return listEtudiants;
    }
    
    

    
    
   /*public class FichierExcel { 

 
     File fichier = new File("D:/NetBeans/fichier_excel.xls") ; 
   
        try{ 

        WritableWorkbook ww = Workbook.createWorkbook(fichier); 

        Sheet wSheet = ww.createSheet("premier", 0); 

        WritableFont arial10font = new WritableFont (WritableFont.ARIAL, 10); 
        WritableCellFormat arial10format = new WritableCellFormat(arial10font); 

        LabelCell label0 = new Label(0, 0, "nom",arial10format ); 
        LabelCell label1 = new Label(1, 0, "prenom",arial10format); 
        LabelCell label2 = new Label(0, 1, "ptit"); 
        LabelCell label3 = new Label(1, 1, "pimousse"); 
     
        wSheet.addCell(label0); 
        wSheet.addCell(label1); 
        wSheet.addCell(label2); 
        wSheet.addCell(label3); 
  
        ww.write(); 
        ww.close(); 
        }catch(IOException e){ 
        }catch(jxl.JXLException er){} 
        } 
    }*/    
    
}
